USE master;
GO

SET NOCOUNT ON;
GO

-- SQL Sever >= 2012
PRINT 'top waits';
;WITH Waits AS
	(SELECT
		wait_type,
		wait_time_ms / 1000.0 AS wait_time_s,
		(wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_time_s,
        signal_wait_time_ms / 1000.0 AS signal_wait_time_s,
		waiting_tasks_count AS wait_count,
		CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS decimal(12,2)) AS wait_percentage,
		ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC) AS row_num
	FROM sys.dm_os_wait_stats WITH (NOLOCK)
	WHERE wait_type NOT IN (
        N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP',
		N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
        N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'CXCONSUMER',
        N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE',
		N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', 
		N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', 
		N'MEMORY_ALLOCATION_EXT', N'ONDEMAND_TASK_QUEUE',
		N'PARALLEL_REDO_DRAIN_WORKER', N'PARALLEL_REDO_LOG_CACHE', N'PARALLEL_REDO_TRAN_LIST',
		N'PARALLEL_REDO_WORKER_SYNC', N'PARALLEL_REDO_WORKER_WAIT_WORK',
		N'PREEMPTIVE_HADR_LEASE_MECHANISM', N'PREEMPTIVE_SP_SERVER_DIAGNOSTICS',
		N'PREEMPTIVE_OS_LIBRARYOPS', N'PREEMPTIVE_OS_COMOPS', N'PREEMPTIVE_OS_CRYPTOPS',
		N'PREEMPTIVE_OS_PIPEOPS', N'PREEMPTIVE_OS_AUTHENTICATIONOPS',
		N'PREEMPTIVE_OS_GENERICOPS', N'PREEMPTIVE_OS_VERIFYTRUST',
		N'PREEMPTIVE_OS_FILEOPS', N'PREEMPTIVE_OS_DEVICEOPS', N'PREEMPTIVE_OS_QUERYREGISTRY',
		N'PREEMPTIVE_OS_WRITEFILE',
		N'PREEMPTIVE_XE_CALLBACKEXECUTE', N'PREEMPTIVE_XE_DISPATCHER',
		N'PREEMPTIVE_XE_GETTARGETSTATE', N'PREEMPTIVE_XE_SESSIONCOMMIT',
		N'PREEMPTIVE_XE_TARGETINIT', N'PREEMPTIVE_XE_TARGETFINALIZE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
		N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
		N'QDS_ASYNC_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH',
		N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP',
		N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP',
		N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
		N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT',
		N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'WAIT_XTP_RECOVERY',
		N'XE_BUFFERMGR_ALLPROCESSED_EVENT', N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT', N'XE_LIVE_TARGET_TVF', N'XE_TIMER_EVENT')
     ),
RunningWaits AS (
	SELECT
		W1.wait_type,
		CAST(W1.wait_time_s AS DECIMAL(14, 2)) AS wait_time_s,
		CAST(W1.resource_wait_time_s AS DECIMAL(14, 2)) AS resource_wait_time_s,
		CAST(W1.signal_wait_time_s AS DECIMAL(14, 2)) AS signal_wait_time_s,
		W1.wait_count,
		CAST ((W1.wait_time_s / W1.wait_count) AS DECIMAL (14, 4)) AS avg_wait_time_s,
		CAST ((W1.resource_wait_time_s / W1.wait_count) AS DECIMAL (14, 4)) AS avg_resource_wait_time_s,
		CAST ((W1.signal_wait_time_s / W1.wait_count) AS DECIMAL (14, 4)) AS avg_signal_wait_time_s,
		W1.wait_percentage,
		SUM(W1.wait_percentage) OVER(ORDER BY W1.wait_percentage DESC ROWS UNBOUNDED PRECEDING) AS running_wait_percentage
	FROM Waits AS W1)
SELECT
	wait_type, wait_percentage, running_wait_percentage, wait_time_s, resource_wait_time_s, signal_wait_time_s, wait_count, avg_wait_time_s, avg_resource_wait_time_s, avg_signal_wait_time_s
FROM RunningWaits
WHERE running_wait_percentage - wait_percentage <= 95 -- percentage threshold
ORDER BY running_wait_percentage ASC OPTION (RECOMPILE);

PRINT 'top latch waits';
;WITH Latches AS
     (SELECT
         latch_class,
         wait_time_ms / 1000.0 AS wait_time_s,
         waiting_requests_count AS wait_count,
         100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS wait_percentage,
         ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS row_num
     FROM sys.dm_os_latch_stats WITH (NOLOCK)
     WHERE latch_class NOT IN (
         'BUFFER')
     AND wait_time_ms > 0
     )
 SELECT
     W1.latch_class AS latch_class, 
     CAST (W1.wait_time_s AS DECIMAL(14, 2)) AS wait_time_s,
     W1.wait_count AS wait_count,
     CAST (W1.wait_percentage AS DECIMAL(14, 2)) AS wait_time_percent,
     CAST ((W1.wait_time_s / W1.wait_count) AS DECIMAL (14, 4)) AS avg_wait_time_s
 FROM Latches AS W1
 INNER JOIN Latches AS W2
     ON W2.row_num <= W1.row_num
 WHERE W1.wait_count > 0
 GROUP BY W1.row_num, W1.latch_class, W1.wait_time_s, W1.wait_count, W1.wait_percentage
 HAVING SUM (W2.wait_percentage) - W1.wait_percentage < 95 OPTION (RECOMPILE); -- percentage threshold